package com.innovation.ic.im.end.base.plugin.manager;

import com.innovation.ic.im.end.base.model.ChatLogs;
import com.innovation.ic.im.end.base.plugin.constant.PluginSql;
import org.apache.commons.lang.StringUtils;
import org.jivesoftware.database.DbConnectionManager;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * 操作数据库
 */
public class DbChatLogsManager {

    private static final Logger Log = LoggerFactory.getLogger(DbChatLogsManager.class);
    private static final DbChatLogsManager CHAT_LOGS_MANAGER = new DbChatLogsManager();

    private DbChatLogsManager() {
    }

    public static DbChatLogsManager getInstance() {
        return CHAT_LOGS_MANAGER;
    }

    /**
     * 获取最后一个id
     * @return
     */
    public int getLastId() {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int count = -1;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(PluginSql.LOGS_LAST_MESSAGE_ID);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                count = rs.getInt(1);
            } else {
                count = 0;
            }
        } catch (SQLException sqle) {
            Log.error(sqle.getMessage(), sqle);
            return 0;
        } finally {
            DbConnectionManager.closeConnection(pstmt, con);
        }
        return count;
    }

    /**
     * 获取总数量
     * @return
     */
    public int getCount() {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int count = -1;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(PluginSql.LOGS_COUNT);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                count = rs.getInt(1);
            } else {
                count = 0;
            }
        } catch (SQLException sqle) {
            Log.error(sqle.getMessage(), sqle);
            return 0;
        } finally {
            DbConnectionManager.closeConnection(pstmt, con);
        }
        return count;
    }

    /**
     * 删除聊天记录信息
     * @param id
     * @return
     */
    public boolean remove(Integer id) {
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(PluginSql.LOGS_REMOVE);
            pstmt.setInt(1, id);
            return pstmt.execute();

        } catch (SQLException sqle) {
            Log.error("chatLogs remove exception: {}", sqle);
            return false;
        } finally {
            DbConnectionManager.closeConnection(pstmt, con);
        }
    }

    /**
     * 添加聊天记录信息
     * @param logs
     * @return
     */
    public boolean add(ChatLogs logs) {
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(PluginSql.LOGS_INSERT);
            int i = 1;
            pstmt.setLong(i++, logs.getMessageId());
            pstmt.setString(i++, logs.getSessionJid());
            pstmt.setString(i++, logs.getSender());
            pstmt.setString(i++, logs.getReceiver());
            pstmt.setTimestamp(i++, logs.getCreateDate());
            pstmt.setInt(i++, logs.getLength());
            pstmt.setString(i++, logs.getContent());
            pstmt.setString(i++, logs.getDetail());
            pstmt.setInt(i++, logs.getState());

            return pstmt.execute();
        } catch (SQLException sqle) {
            Log.error("chatLogs add exception: {}", sqle);
            return false;
        } finally {
            DbConnectionManager.closeConnection(pstmt, con);
        }
    }

    /**
     * 通过id查询聊天记录信息
     * @param id
     * @return
     */
    public ChatLogs find(int id) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ChatLogs logs = null;

        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(PluginSql.LOGS_FIND_BY_ID);
            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                logs = new ChatLogs();
                logs.setMessageId(rs.getInt("message_id"));
                logs.setContent(rs.getString("content"));
                logs.setCreateDate(rs.getTimestamp("create_date"));
                logs.setLength(rs.getInt("length"));
                logs.setSessionJid(rs.getString("session_jid"));
                logs.setSender(rs.getString("sender"));
                logs.setReceiver(rs.getString("receiver"));
            }
            return logs;
        } catch (SQLException sqle) {
            Log.error("chatLogs find exception: {}", sqle);
            return logs;
        } finally {
            DbConnectionManager.closeConnection(pstmt, con);
        }
    }

    /**
     * 多条件搜索查询，返回List&lt;ChatLogs>集合
     * @param entity
     * @return
     */
    public List<ChatLogs> query(ChatLogs entity) {
        Connection con = null;
        Statement pstmt = null;
        ChatLogs logs = null;

        List<ChatLogs> result = new ArrayList<ChatLogs>();

        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.createStatement();

            String sql = PluginSql.LOGS_QUERY;
            if (entity != null) {
                if (!StringUtils.isEmpty(entity.getSender()) && !StringUtils.isEmpty(entity.getReceiver())) {
                    sql += " and (sender = '" + entity.getSender() + "' and receiver = '" + entity.getReceiver() + "')";
                    sql += " or (receiver = '" + entity.getSender() + "' and sender = '" + entity.getReceiver() + "')";

                } else {
                    if (!StringUtils.isEmpty(entity.getSender())) {
                        sql += " and sender = '" + entity.getSender() + "'";
                    }
                    if (!StringUtils.isEmpty(entity.getReceiver())) {
                        sql += " and receiver = '" + entity.getReceiver() + "'";
                    }
                }


                if (!StringUtils.isEmpty(entity.getContent())) {
                    sql += " and content like '%" + entity.getContent() + "%'";
                }
                if (entity.getCreateDate() != null) {
                    DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
                    String crateatDate = df.format(new Date(entity.getCreateDate().getTime()));
                    //sql += " and to_char(create_date, 'yyyy-mm-dd') = '" + crateatDate + "'";
                    sql += " and create_date like '" + crateatDate + "%'";
                }
            }
            sql += " order by create_date asc";
            ResultSet rs = pstmt.executeQuery(sql);
            while (rs.next()) {
                logs = new ChatLogs();
                logs.setMessageId(rs.getInt("message_id"));
                logs.setContent(rs.getString("content"));
                logs.setCreateDate(rs.getTimestamp("create_date"));
                logs.setLength(rs.getInt("length"));
                logs.setSessionJid(rs.getString("session_jid"));
                logs.setSender(rs.getString("sender"));
                logs.setReceiver(rs.getString("receiver"));
                result.add(logs);
            }
            return result;
        } catch (SQLException sqle) {
            Log.error("chatLogs search exception: {}", sqle);
            return result;
        } finally {
            DbConnectionManager.closeConnection(pstmt, con);
        }
    }

    /**
     * 多条件搜索查询，返回List<Map>集合
     * @param entity
     * @return
     */
    public List<HashMap<String, Object>> search(ChatLogs entity) {
        Connection con = null;
        Statement pstmt = null;

        List<HashMap<String, Object>> result = new ArrayList<HashMap<String, Object>>();

        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.createStatement();

            String sql = PluginSql.LOGS_SEARCH;
            if (entity != null) {
                if (!StringUtils.isEmpty(entity.getSender()) && !StringUtils.isEmpty(entity.getReceiver())) {
                    sql += " and (sender = '" + entity.getSender() + "' and receiver = '" + entity.getReceiver() + "')";
                    sql += " or (receiver = '" + entity.getSender() + "' and sender = '" + entity.getReceiver() + "')";

                } else {
                    if (!StringUtils.isEmpty(entity.getSender())) {
                        sql += " and sender = '" + entity.getSender() + "'";
                    }
                    if (!StringUtils.isEmpty(entity.getReceiver())) {
                        sql += " and receiver = '" + entity.getReceiver() + "'";
                    }
                }
                if (!StringUtils.isEmpty(entity.getContent())) {
                    sql += " and content like '%" + entity.getContent() + "%'";
                }
                if (entity.getCreateDate() != null) {
                    DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
                    String crateatDate = df.format(new Date(entity.getCreateDate().getTime()));
                    sql += " and to_char(create_date, 'yyyy-mm-dd') = '" + crateatDate + "'";
                }
            }
            sql += " order by create_date asc";
            ResultSet rs = pstmt.executeQuery(sql);

            ResultSetMetaData rsmd = rs.getMetaData();
            /** 获取结果集的列数*/
            int columnCount = rsmd.getColumnCount();
            while (rs.next()) {
                HashMap<String, Object> map = new HashMap<String, Object>();
                /** 把每一行以(key, value)存入HashMap, 列名做为key,列值做为value */
                for (int i = 1; i <= columnCount; ++i) {
                    String columnVal = rs.getString(i);
                    if (columnVal == null) {
                        columnVal = "";
                    }
                    map.put(rsmd.getColumnName(i), columnVal);
                }
                /** 把装有一行数据的HashMap存入list */
                result.add(map);
            }
            return result;
        } catch (SQLException sqle) {
            Log.error("chatLogs search exception: {}", sqle);
            return result;
        } finally {
            DbConnectionManager.closeConnection(pstmt, con);
        }
    }

    /**
     * 最近联系人
     * @param entity
     * @return
     */
    public List<String> findLastContact(ChatLogs entity) {
        Connection con = null;
        PreparedStatement pstmt = null;
        List<String> result = new ArrayList<String>();

        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(PluginSql.LOGS_LAST_CONTACT);
            pstmt.setString(1, entity.getSender());
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                result.add(rs.getString("receiver"));
            }
            return result;
        } catch (SQLException sqle) {
            Log.error("chatLogs find exception: {}", sqle);
            return result;
        } finally {
            DbConnectionManager.closeConnection(pstmt, con);
        }
    }

    /**
     * 查找所有聊天用户
     * @return
     */
    public List<String> findAllContact() {
        Connection con = null;
        PreparedStatement pstmt = null;
        List<String> result = new ArrayList<String>();

        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(PluginSql.LOGS_ALL_CONTACT);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                result.add(rs.getString("session_jid"));
            }
            return result;
        } catch (SQLException sqle) {
            Log.error("chatLogs find exception: {}", sqle);
            return result;
        } finally {
            DbConnectionManager.closeConnection(pstmt, con);
        }
    }
}
